package com.lovestudying.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;


public class DBUtil {
	
	// 对常量进行封装
	private static final String DRIVER = "com.mysql.jdbc.Driver";
	private static final String URL = "jdbc:mysql://localhost:3306/studyingsystem?useUnicode=true&characterEncoding=UTF-8&useOldAliasMetadataBehavior=true&useAffectedRows=true&serverTimezone=GMT";
	private static final String USER = "root";
	private static final String PASSWORD = "root";
	
	/**
	 * 封装加载驱动、连接数据库。
	 */
	private static Connection getConnection() {
		Connection conn = null;
		try {
			// 加载驱动
			Class.forName(DRIVER);
			
			// 连接数据库
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return conn;
	}
	
	
	/**
	 * 关闭所有资源
	 */
	public static void closeAll(Connection conn,PreparedStatement ps,ResultSet rs) {
		
		try {
			// 先开的后关
			if(rs != null) {
				rs.close();
			}
			if(ps != null) {
				ps.close();
			}
			if(conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
//	执行SELECT查询语句
	public static ResultSet doQuery(String sql,Object...params) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;// 结果集
		CachedRowSet cachedRowSet = null;	// 缓存结果集
		try {
			// 连接数据库
			conn = getConnection();
			// 创建预编译语句对象
			ps = conn.prepareStatement(sql);
			// 通过循环可变参数数组,对?赋值
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i+1, params[i]); 
			}
			// 执行SQL-->得到结果集
			rs = ps.executeQuery();
			
			cachedRowSet = new CachedRowSetImpl();
			cachedRowSet.populate(rs);	// void populate(ResultSet data) 将ResultSet对象中的数据存储到缓存结果集对象中
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 关闭资源
			closeAll(conn, ps, rs);
		}
		return cachedRowSet;  //返回缓存结果集对象,CachedRowSet是继承于ResultSet
	}

	/**
	 * 进行查询操作
	 */
	public static ResultSet find(String sql,Object...params) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		CachedRowSet rowSet = null;
		
		try {
			// 加载驱动、数据库连接
			conn = getConnection();
			
			// 创建PreparedStatement预编译语句对象
			ps = conn.prepareStatement(sql);
			
			// 当对参数params赋值时
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i + 1 , params[i]);
			}
			
			// 进行查询操作
			rs = ps.executeQuery();
			
			// CachedRowSet接口指向实现类CachedRowSetImpl
			rowSet = new CachedRowSetImpl();
			
			// 把结果集传入缓存结果集
			rowSet.populate(rs);
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			// 关闭资源
			closeAll(conn, ps, rs);
		}
		return rowSet;
	}
	
	/**
	 * 进行插入、删除、修改操作
	 */
	public static int update(String sql,Object...params) {
		Connection conn = null;
		PreparedStatement ps = null;
		int n = 0;
		
		try {
			// 加载驱动、连接数据路
			conn = getConnection();
			
			// 创建PreparedStatement预编译语句对象
			ps = conn.prepareStatement(sql);
						
			// 当对参数params赋值时
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i + 1 , params[i]);
			}
						
			// 进行插入、删除、修改操作
			n = ps.executeUpdate(); 
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			closeAll(conn, ps, null);
		}
		return n;
	}
	
	/**
	 * 
	 * @authod Linzhengxiang
	 */
	public static List<Map<String, Object>> ResultSetToList(ResultSet rs) throws SQLException {
		List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
		ResultSetMetaData rsmd = rs.getMetaData();
		int colCount = rsmd.getColumnCount();
		List<String> colNameList = new ArrayList<String>();
		for (int i = 0; i < colCount; i++) {
			colNameList.add(rsmd.getColumnLabel(i + 1));
//			debug(" colNameList = "+rsmd.getColumnLabel(i+1));
		}

		while (rs.next()) {
			Map map = new HashMap<String, Object>();
			for (int i = 0; i < colCount; i++) {

				String key = colNameList.get(i);
				Object value = rs.getString(colNameList.get(i));
//				System.out.println("key = "+key+" value ="+value );
				map.put(key, value);
			}
			results.add(map);
		}
		rs.beforeFirst();// 灏嗙粨鏋滈泦鎸囬拡鎸囧洖鍒板紑濮嬩綅缃紝杩欐牱鎵嶈兘閫氳繃while鑾峰彇rs涓殑鏁版嵁
		return results;
	}



}
